package cn.wanda.utils.sql

trait TmpTableSQL {
    /**
      * oracle获取的原表
      */
    val namelist: String =
        """
          |(select a.*
          |,trunc(sysdate) as fenan_date
          |from(
          |   select member_code,max_due_date
          |   from (
          |     select
          |       distinct lc.external_id as member_code,
          |       max(t1.due_date) as max_due_date
          |from (
          |     select lit.cust_nbr,
          |        max(case when litd.pymt_flag in ('00','04') and lit.status='4' then trunc(sysdate)-trunc(litd.payment_dte) else 0 end) as due_date
          |       from wise.lm_installment_tran_d litd
          |       inner join  wise.lm_installment_tran   lit
          |       on litd.account_nmbr=lit.account_nmbr
          |       where  lit.STATUS in('0', '4')
          |       and litd.crt_time between trunc(sysdate)-100 and trunc(sysdate)+1
          |       group by lit.cust_nbr
          |       union
          |       select b.cust_nbr
          |       ,max(case when b.status=4 then trunc(sysdate)-trunc(a.payment_date) else 0 end )as due_date
          |       from wise.ts_installment_detail a
          |	   inner join wise.ts_installment_main b
          |       on a.installment_id=b.id
          |       where b.status in(0,4)
          |       group by b.cust_nbr
          |       ) t1
          |       inner join wise.lm_customer lc
          |       on t1.cust_nbr=lc.cust_nbr
          |       group by  lc.external_id
          |       )
          |    )a
          |left join (select distinct member_code from wise.fcs_txn where app_id in ('ZJDLWDD','ZJDLWED'))b
          |on a.member_code=b.member_code
          |where b.member_code is null
          |and max_due_date=1) t
        """.stripMargin

    val whmd_hisdue1_11: String =
        """
          |(select y.member_code
          |--,y.name
          |--,y.gender
          |,y.fenan_date
          |,y.cust_nbr
          |,y.submit_time
          |,y.audit_time
          |,y.cash_amt
          |,y.cash_level
          |,y.status as reg_status
          |,months_between(y.fenan_date,trunc(y.audit_time))     as audit_mob
          |,account_nmbr
          |,TOT_TERMS
          |,DELQ_CYCLE
          |,transaction_type
          |,principal
          |,repayment_time
          |,to_date(to_char(payment_dte,'yyyymmdd')||' '||substr(to_char(posting_dte,'yyyymmdd hh24:mi:ss'),10),'yyyymmdd hh24:mi:ss') as payment_dte
          |,posting_dte
          |,lst_upd_time1 as lst_upd_time
          |,round(tran_amt,0) as tran_amt
          |,round(tran_amt_paid,0) as tran_amt_paid
          |,pymt_flag
          |,overdue_flag
          |,term
          |,src
          |--分案前一天的逾期天数,不包括当前入催的逾期
          |,case when trunc(payment_dte)<y.fenan_date and pymt_flag=0 then y.fenan_date-trunc(payment_dte)
          |      when trunc(payment_dte)<y.fenan_date and pymt_flag=1 and  trunc(lst_upd_time1)<trunc(fenan_date) then trunc(lst_upd_time1)-trunc(payment_dte)
          |      when trunc(payment_dte)<y.fenan_date and pymt_flag=1 and  trunc(lst_upd_time1)>=trunc(fenan_date) then trunc(fenan_date)-trunc(payment_dte)
          |      else null
          | end as due
          |from
          |(
          |select xx.member_code,xx.fenan_date
          |,a.cust_nbr,a.submit_time,a.audit_time,a.cash_amt,a.cash_level,a.status
          |,b.account_nmbr,b.TOT_TERMS,b.DELQ_CYCLE,b.transaction_type,b.principal
          |,c.repayment_time,c.payment_dte,c.posting_dte,c.tran_amt,c.tran_amt_paid,c.pymt_flag,c.overdue_flag
          |,c.term,c.src
          |,case when trunc(c.lst_upd_time)=to_date('20880808','yyyymmdd') then xx.fenan_date else lst_upd_time end as lst_upd_time1
          |from (select a.*
          |,trunc(sysdate) as fenan_date
          |from(
          |   select member_code,max_due_date
          |   from (
          |     select
          |       distinct lc.external_id as member_code,
          |       max(t1.due_date) as max_due_date
          |from (
          |     select lit.cust_nbr,
          |        max(case when litd.pymt_flag in ('00','04') and lit.status='4' then trunc(sysdate)-trunc(litd.payment_dte) else 0 end) as due_date
          |       from wise.lm_installment_tran_d litd
          |       inner join  wise.lm_installment_tran   lit
          |       on litd.account_nmbr=lit.account_nmbr
          |       where  lit.STATUS in('0', '4')
          |       and litd.crt_time between trunc(sysdate)-100 and trunc(sysdate)+1
          |       group by lit.cust_nbr
          |       union
          |       select b.cust_nbr
          |       ,max(case when b.status=4 then trunc(sysdate)-trunc(a.payment_date) else 0 end )as due_date
          |       from wise.ts_installment_detail a
          |	   inner join wise.ts_installment_main b
          |       on a.installment_id=b.id
          |       where b.status in(0,4)
          |       group by b.cust_nbr
          |       ) t1
          |       inner join wise.lm_customer lc
          |       on t1.cust_nbr=lc.cust_nbr
          |       group by  lc.external_id
          |       )
          |    )a
          |left join (select distinct member_code from wise.fcs_txn where app_id in ('ZJDLWDD','ZJDLWED'))b
          |on a.member_code=b.member_code
          |where b.member_code is null
          |and max_due_date=1) xx
          |inner join (
          |  select member_code,cust_nbr,submit_time,audit_time,cash_amt,cash_level,status  from wise.fcs_acct_register
          |)a
          |on xx.member_code=a.member_code
          |
          |inner join (
          |  select cust_nbr,status,account_nmbr,TOT_TERMS,DELQ_CYCLE,transaction_type,principal from wise.lm_installment_tran
          |  where status<>'5' and transaction_type='3'
          |  union all
          |  select cust_nbr,to_char(status),to_char(id) as account_nmbr,TOTAL_TERMS,OVERDUE_TERMS as DELQ_CYCLE ,null as transaction_type,tran_amt as principal from  wise.ts_installment_main where status in (0,1,2,4)
          |  )b  on a.cust_nbr=b.cust_nbr
          |
          |
          |inner join (
          |  select null as status
          |  ,to_number(pymt_flag) as pymt_flag
          |  --,case when trunc(lst_upd_time)=to_date('20181203','yyyymmdd')  and tran_amt_paid=0 and pymt_flag='01'  then 0  else to_number(pymt_flag)  end as pymt_flag
          |  ,account_nmbr,repayment_time,payment_dte
          |  ,case when pymt_flag='00'  or ( trunc(lst_upd_time)=to_date('20181203','yyyymmdd')  and tran_amt_paid=0 and pymt_flag='01' ) then to_date('20880808','yyyymmdd') else lst_upd_time end as lst_upd_time
          |  ,tran_amt,tran_amt_paid
          |  ,case when DELQ_STATUS='1' then 1 else 0 end  as overdue_flag
          |  ,posting_dte
          |  ,term
          |  ,'lm_tran_d' as src
          |  from wise.lm_installment_tran_d
          |  where tran_type='4000'
          |  and PYMT_FLAG not in ('02','03')
          |  and extend_flag=0
          |
          |  union all
          |  select status
          |  ,case when status=1 then 1 else 0 end as pymt_flag
          |  --,case when trunc(upd_time)=to_date('20181203','yyyymmdd')  and tran_amt_paid=0 and status=1  then 0 else status end as pymt_flag
          |  ,to_char(INSTALLMENT_ID) as account_nmbr,repay_time  as repayment_time ,PAYMENT_DATE  as payment_dte
          |  ,case when repay_time is null then to_date('20880808','yyyymmdd') else upd_time end as lst_upd_time
          |  ,tran_amt,tran_amt_paid
          |  ,overdue_flag
          |  ,txn_time as posting_dte
          |  ,term
          |  ,'ts_detail' as src
          |  from  wise.ts_installment_detail
          |  where status in (0,1,2,4)
          |  )c on b.account_nmbr=c.account_nmbr
          |where trunc(posting_dte)<xx.fenan_date
          |)y) t
        """.stripMargin

    val whmd_hisdue1_11_2: String =
        """
          |(select y.member_code
          |--,y.name
          |--,y.gender
          |,y.fenan_date
          |,y.cust_nbr
          |,y.submit_time
          |,y.audit_time
          |,y.cash_amt
          |,y.cash_level
          |,y.status as reg_status
          |,months_between(y.fenan_date,trunc(y.audit_time))     as audit_mob
          |,account_nmbr
          |,TOT_TERMS
          |,DELQ_CYCLE
          |,transaction_type
          |,principal
          |,repayment_time
          |,to_date(to_char(payment_dte,'yyyymmdd')||' '||substr(to_char(posting_dte,'yyyymmdd hh24:mi:ss'),10),'yyyymmdd hh24:mi:ss') as payment_dte
          |,posting_dte
          |,lst_upd_time1 as lst_upd_time
          |,round(tran_amt,0) as tran_amt
          |,round(tran_amt_paid,0) as tran_amt_paid
          |,pymt_flag
          |,overdue_flag
          |,term
          |,src
          |--分案前一天的逾期天数,不包括当前入催的逾期
          |,case when trunc(payment_dte)<y.fenan_date and pymt_flag=0 then y.fenan_date-trunc(payment_dte)
          |      when trunc(payment_dte)<y.fenan_date and pymt_flag=1 and  trunc(lst_upd_time1)<trunc(fenan_date) then trunc(lst_upd_time1)-trunc(payment_dte)
          |      when trunc(payment_dte)<y.fenan_date and pymt_flag=1 and  trunc(lst_upd_time1)>=trunc(fenan_date) then trunc(fenan_date)-trunc(payment_dte)
          |      else null
          | end as due
          |from
          |(
          |select xx.member_code,xx.fenan_date
          |,a.cust_nbr,a.submit_time,a.audit_time,a.cash_amt,a.cash_level,a.status
          |,b.account_nmbr,b.TOT_TERMS,b.DELQ_CYCLE,b.transaction_type,b.principal
          |,c.repayment_time,c.payment_dte,c.posting_dte,c.tran_amt,c.tran_amt_paid,c.pymt_flag,c.overdue_flag
          |,c.term,c.src
          |,case when trunc(c.lst_upd_time)=to_date('20880808','yyyymmdd') then xx.fenan_date else lst_upd_time end as lst_upd_time1
          |from wise.namelist_2 xx
          |inner join (
          |  select member_code,cust_nbr,submit_time,audit_time,cash_amt,cash_level,status  from wise.fcs_acct_register
          |)a
          |on xx.member_code=a.member_code
          |
          |inner join (
          |  select cust_nbr,status,account_nmbr,TOT_TERMS,DELQ_CYCLE,transaction_type,principal from wise.lm_installment_tran
          |  where status<>'5' and transaction_type='3'
          |  union all
          |  select cust_nbr,to_char(status),to_char(id) as account_nmbr,TOTAL_TERMS,OVERDUE_TERMS as DELQ_CYCLE ,null as transaction_type,tran_amt as principal from  wise.ts_installment_main where status in (0,1,2,4)
          |  )b  on a.cust_nbr=b.cust_nbr
          |
          |
          |inner join (
          |  select null as status
          |  ,to_number(pymt_flag) as pymt_flag
          |  --,case when trunc(lst_upd_time)=to_date('20181203','yyyymmdd')  and tran_amt_paid=0 and pymt_flag='01'  then 0  else to_number(pymt_flag)  end as pymt_flag
          |  ,account_nmbr,repayment_time,payment_dte
          |  ,case when pymt_flag='00'  or ( trunc(lst_upd_time)=to_date('20181203','yyyymmdd')  and tran_amt_paid=0 and pymt_flag='01' ) then to_date('20880808','yyyymmdd') else lst_upd_time end as lst_upd_time
          |  ,tran_amt,tran_amt_paid
          |  ,case when DELQ_STATUS='1' then 1 else 0 end  as overdue_flag
          |  ,posting_dte
          |  ,term
          |  ,'lm_tran_d' as src
          |  from wise.lm_installment_tran_d
          |  where tran_type='4000'
          |  and PYMT_FLAG not in ('02','03')
          |  and extend_flag=0
          |
          |  union all
          |  select status
          |  ,case when status=1 then 1 else 0 end as pymt_flag
          |  --,case when trunc(upd_time)=to_date('20181203','yyyymmdd')  and tran_amt_paid=0 and status=1  then 0 else status end as pymt_flag
          |  ,to_char(INSTALLMENT_ID) as account_nmbr,repay_time  as repayment_time ,PAYMENT_DATE  as payment_dte
          |  ,case when repay_time is null then to_date('20880808','yyyymmdd') else upd_time end as lst_upd_time
          |  ,tran_amt,tran_amt_paid
          |  ,overdue_flag
          |  ,txn_time as posting_dte
          |  ,term
          |  ,'ts_detail' as src
          |  from  wise.ts_installment_detail
          |  where status in (0,1,2,4)
          |  )c on b.account_nmbr=c.account_nmbr
          |where trunc(posting_dte)<xx.fenan_date
          |)y) t
        """.stripMargin

    /**
      * 中间临时表
      */

    val whmd_hisdue: String =
        """
          |select a.*
          |,max_due
          |,cash_level
          |,cash_amt
          |from namelist a
          |left join (
          |  select member_code,cash_level,cash_amt,max(due) as max_due from whmd_hisdue1_11
          |  -- trunc modify date_format
          |  where date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),1)
          |  group by member_code,cash_level,cash_amt
          |)b on a.member_code=b.member_code
        """.stripMargin

    val checklist_tmp2: String =
        """
          |select y.*
          |from(select pay_user_id,max(create_time) as crt_time from checklist_tmp group by pay_user_id)x
          |left join checklist_tmp y
          |on x.pay_user_id=y.pay_user_id
          |and x.crt_time=y.create_time
        """.stripMargin

    val checklist_tmp3: String =
        """
          |select a.pay_user_id
          |,a.rule_package_name
          |,a.rule_id
          |,a.score_new
          |,a.tdscore
          |,case when a.rule_id in ('2773861','7115061','7115311','10076817','10076627','7112971','12380247','14529627','7112591','11586763','14529567','14529817','2263806','7112841','10076567','11586773','2738181','13699807','2672855','2738303','13699747','2263818','2672867','13699997','2738279')
          | then score_new else 0 end as td_90d
          |,case when a.rule_id in ('14529647','10076647','2263822','13699827','2672871','2263808')  then score_new else 0 end as td_180d
          |,case when a.rule_id in ('65273','65269','65265','65260')  then score_new else 0 end as bqs_90d
          |from checklist_tmp2 a
        """.stripMargin

    val checklist_tmp4: String =
        """
          |select pay_user_id
          |,max(tdscore) as tdscore
          |,max(td_90d) td_90d
          |,max(td_180d) td_180d
          |,max(bqs_90d) bqs_90d
          |,sum(case when RULE_PACKAGE_NAME not in ('关联匹配类规则','异常借款_主动进件','异常借款_营销客户','数据服务类规则','行为数据类规则','通讯录规则') and RULE_PACKAGE_NAME not like '%测试%' then score_new else 0 end ) as rule_els
          |,sum(case when rule_id in ('txl005','txl009','txl010','txl032','txl035','txl036','txl034','txl033','txl037','txl038','txl030','txl042') then 1 else 0 end)            as txl_cnt
          |,count(*) as total_cnt
          |from checklist_tmp3 a
          |group by pay_user_id
        """.stripMargin

    val whmd_tran_tmp: String =
        """
          |select x.member_code,date_format(x.earliest_posting_dte, "yyyy-MM-dd") as earliest_posting_dte from
          |(select  a.member_code,a.posting_dte as earliest_posting_dte,row_number() over(partition by member_code order by posting_dte asc) as rn
          |from whmd_hisdue1_11 a
          |)x
          |where x.rn=1
        """.stripMargin

    val whmd_tran_tmp2: String =
        """
          |select x.member_code,date_format(x.lst_posting_dte, "yyyy-MM-dd") as lst_posting_dte from
          |(select  a.member_code,a.posting_dte as lst_posting_dte,row_number() over(partition by member_code order by posting_dte desc) as rn
          |from whmd_hisdue1_11 a
          |)x
          |where x.rn=1
        """.stripMargin

    val A_shidong11: String =
        """
          |select a.member_code
          |,round((sum(case when date_format(posting_dte, "yyyy-MM-dd")<=date_format(b.earliest_posting_dte, "yyyy-MM-dd") then tran_amt else 0 end)-sum(case when date_format(lst_upd_time, "yyyy-MM-dd")<=date_format(b.earliest_posting_dte, "yyyy-MM-dd")   then tran_amt_paid else 0 end))/max(cash_amt),2) as earlist_shidong_rate
          |,round((sum(case when date_format(posting_dte, "yyyy-MM-dd")<date_format(c.lst_posting_dte, "yyyy-MM-dd") then tran_amt else 0 end)-sum(case when date_format(lst_upd_time, "yyyy-MM-dd")<date_format(c.lst_posting_dte, "yyyy-MM-dd")  and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd")  then tran_amt_paid else 0 end))/max(cash_amt),2) as lst_shidong_rate2
          |from whmd_hisdue1_11 a
          |left join whmd_tran_tmp b
          |on a.member_code=b.member_code
          |left join whmd_tran_tmp2 c
          |on a.member_code=c.member_code
          |group by a.member_code
        """.stripMargin

    val whmd_shouyu: String =
        """
          |select distinct a.member_code from
          |(select * from whmd_hisdue1_11 where date_add(date_format(payment_dte, "yyyy-MM-dd"),1)=date_format(fenan_date, "yyyy-MM-dd") and
          |term=1) a
          |inner join whmd_tran_tmp b
          |on a.member_code=b.member_code
          |and date_format(a.posting_dte, "yyyy-MM-dd")=date_format(b.earliest_posting_dte, "yyyy-MM-dd")
        """.stripMargin

    val whmd_tran_var: String =
        """
          |select member_code
          |,sum(case when date_format(posting_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12) and date_format(posting_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then tran_amt else 0 end) as trade_amt12
          |,sum(distinct case when pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)  and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then tran_amt else 0 end) as tran_paid_amt12
          |,sum(distinct case when pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*1)  and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then tran_amt else 0 end) as tran_paid_amt1
          |,sum(distinct case when pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*2)  and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*1) then tran_amt else 0 end) as tran_paid_amt2
          |
          |--每月逾期应还到期次数
          |,count(distinct case when overdue_flag=1 and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*1)   and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then payment_dte else null end) as od_tran_cnt1
          |,count(distinct case when pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)  and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then payment_dte else null end) as tran_paid_cnt12
          |
          |--每月逾期已还次数
          |,count(distinct case when overdue_flag=1 and pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*1)   and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then payment_dte else null end) as od_paid_cnt1
          |,count(distinct case when overdue_flag=1 and pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*2)   and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*1) then payment_dte else null end) as od_paid_cnt2
          |,count(distinct case when overdue_flag=1 and pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*3) and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*2) then payment_dte else null end) as od_paid_cnt3
          |
          |,count(distinct case when overdue_flag=1 and pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)  and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then payment_dte else null end) as od_paid_cnt12
          |
          |,sum(case when overdue_flag=1 and pymt_flag =1 and date_format(lst_upd_time, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd") and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)  and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then tran_amt else 0 end) as od_paid_amt12
          |
          |,sum(case when overdue_flag=1 and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*1) and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then tran_amt else 0 end) as od_tran_amt1
          |--每月逾期交易次数
          |,count(distinct case when overdue_flag=1 and date_format(posting_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*3)   and date_format(posting_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then posting_dte else null end) as od_trade_cnt3
          |,sum(case when overdue_flag=1 and date_format(posting_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*3)   and date_format(posting_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*0) then tran_amt else  0 end) as od_trade_amt3
          |,count(distinct case when due>0 and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)  and date_format(payment_dte, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd")-1 then payment_dte else null end) as od_tot_cnt_m12
          |
          |--近期还款总逾期天数
          |,sum(case when due>0 and date_format(payment_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)  and date_format(payment_dte, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd")-1 then due else 0 end) as od_tot_day_m12
          |from whmd_hisdue1_11
          |where date_format(posting_dte, "yyyy-MM-dd")>=date_sub(date_format(fenan_date, "yyyy-MM-dd"),30*12)
          |and date_format(posting_dte, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd")
          |and date_format(payment_dte, "yyyy-MM-dd")<date_format(fenan_date, "yyyy-MM-dd")
          |group by member_code
        """.stripMargin

    val tran_var_finl: String =
        """
          |select member_code
          |,case when tran_paid_amt2 is null or tran_paid_amt2=0 then null else tran_paid_amt1/tran_paid_amt2 end as tran_paid_amt_incre_1mth
          |,case when trade_amt12 is null or trade_amt12=0 then null else tran_paid_amt12/trade_amt12 end as TRAN_PAID_AMT_PRt_12mth
          |,od_tran_cnt1                        as od_tran_cnt_1mth
          |,case when od_tot_cnt_m12 is null or od_tot_cnt_m12=0 then null else od_tot_day_m12/od_tot_cnt_m12 end as od_avg_day_12mth
          |,case when od_paid_cnt2 is null or od_paid_cnt2=0 or od_paid_cnt3=0 or od_paid_cnt3 is null then  null else (od_paid_cnt1/od_paid_cnt2) / (od_paid_cnt2/od_paid_cnt3) end as OD_PAID_AMT_incre_trend
          |,od_tran_amt1                   --/cash_amt as OD_TRAN_AMT_CRt_1mth
          |,od_trade_cnt3                       as od_trade_cnt_3mth
          |,case when tran_paid_cnt12 is null or tran_paid_cnt12 =0 then null else od_paid_cnt12/tran_paid_cnt12  end as od_paid_ratio_12mth
          |,case when trade_amt12 is null or trade_amt12=0 then null else  od_paid_amt12/trade_amt12  end as OD_PAID_AMT_PRT_12mth
          |from whmd_tran_var
        """.stripMargin

    val whmd_tran_tmp3: String =
        """
          |select x.member_code,date_format(x.earliest_posting_dte, "yyyy-MM-dd") as earliest_posting_dte from
          |(select  a.member_code,a.posting_dte as earliest_posting_dte,row_number() over(partition by member_code order by posting_dte asc) as rn
          |from whmd_hisdue1_11 a
          |)x
          |where x.rn=1
        """.stripMargin

    val A_shidong: String =
        """
          |select b.member_code
          |,round((sum(tran_amt)-sum(case when lst_upd_time<fenan_date then tran_amt_paid else 0 end))/max(cash_amt),2) as shidong_rate
          |from whmd_hisdue1_11 a
          |left join whmd_tran_tmp3 b
          |on a.member_code=b.member_code
          |group by b.member_code
        """.stripMargin

    val A_day1: String =
        """
          |select *
          |from
          |(
          |select a.*,row_number() over(partition by member_code order by payment_dte desc ) as rn2 from whmd_hisdue1_11 a
          |where due>=1 and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),1)
          |)x  where x.rn2=1
        """.stripMargin

    val A_day: String =
        """
          |select a.member_code
          |,(datediff(date_format(a.fenan_date, "yyyy-MM-dd"),date_format(a.payment_dte, "yyyy-MM-dd"))) as lst_od_dob
          |,b.lst_od_dob3
          |from A_day1 a
          |--最近一次逾期3天以上距今天数
          |left join (
          |select x.member_code, datediff(date_format(x.fenan_date, "yyyy-MM-dd"), (date_format(x.payment_dte, "yyyy-MM-dd"))) as lst_od_dob3
          |from
          |(
          |select a.*,row_number() over(partition by member_code order by payment_dte desc ) as rn2 from whmd_hisdue1_11 a
          |where due>=3 and date_format(payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),1)
          |)x  where x.rn2=1
          |) b
          |on a.member_code=b.member_code
        """.stripMargin

    val lst_txn_dtl: String =
        """
          |select b.*,a.lst_posting_dte
          |from
          |(select member_code,posting_dte as lst_posting_dte
          |from
          |(
          |select member_code,posting_dte,row_number() over(partition by member_code order by posting_dte desc ) as rn from whmd_hisdue1_11
          |where date_format(posting_dte,"yyyy-MM-dd")<date_sub(date_format(fenan_date,"yyyy-MM-dd"),1)
          |)a1  where a1.rn=1
          |)a
          |left join whmd_hisdue1_11 b
          |on a.member_code=b.member_code
          |where date_format(b.payment_dte, "yyyy-MM-dd")>=date_format(a.lst_posting_dte, "yyyy-MM-dd")     --到期时间在最近一次交易之后
          |and date_format(b.payment_dte, "yyyy-MM-dd")<date_sub(date_format(fenan_date, "yyyy-MM-dd"),1)                    --到期时间小于分案时间,即已到期
        """.stripMargin

    val lst_txn_var: String =
        """
          |select
          |member_code
          |,lst_posting_dte
          |,min(datediff(date_format(fenan_date, "yyyy-MM-dd"),date_format(posting_dte, "yyyy-MM-dd"))) as lst_txn_dob
          |from lst_txn_dtl
          |group by member_code,lst_posting_dte
        """.stripMargin

    /**
      * 合成大表
      */
    val ccard_bigtable11: String =
        """
          |select xx.MEMBER_CODE as member_code1
          |,c1.earlist_shidong_rate,c1.lst_shidong_rate2
          |,d.td_90d,d.td_180d,d.bqs_90d,d.txl_cnt,d.rule_els,d.total_cnt
          |,a.*
          |,a.od_tran_amt1/e.cash_amt as od_tran_amt_crt_1mth
          |,c.lst_txn_dob
          |,c0.shidong_rate
          |,e.max_due
          |,case when f.member_code is not null then 1 else 0 end as shouyu
          |,g.lst_od_dob3
          |,oim.od_intvl_max
          |,av3.shidong_avg_3mon
          |from namelist xx
          |left join  tran_var_finl a
          |on xx.member_code=a.member_code
          |left join lst_txn_var c
          |on xx.member_code=c.member_code
          |left join A_shidong c0
          |on xx.member_code=c0.member_code
          |left join  A_shidong11 c1
          |on xx.member_code=c1.member_code
          |left join checklist_tmp4 d
          |on xx.member_code=d.pay_user_id
          |left join whmd_hisdue e
          |on xx.member_code=e.member_code
          |left join whmd_shouyu f
          |on xx.member_code=f.member_code
          |left join a_day  g
          |on xx.member_code=g.member_code
          |left join od_intvl_max oim
          |on xx.member_code=oim.member_code
          |left join shidong_avg_3mon av3
          |on xx.member_code=av3.member_code
        """.stripMargin

    /**
      * 作业内用到的SQL
      */
    val group1Sql: String =
        """
          |select member_code1, td_180d, od_tran_amt_crt_1mth
          |from ccard_bigtable11
          |where shouyu = 1
        """.stripMargin

    val group2Sql: String =
        """
          |select member_code1, bqs_90d, od_tran_cnt_1mth, shidong_avg_3mon, total_cnt, txl_cnt, shidong_rate
          |from ccard_bigtable11
          |where shouyu = 0 and max_due = 0
        """.stripMargin

    val group3Sql: String =
        """
          |select member_code1, lst_od_dob3, earlist_shidong_rate, lst_shidong_rate2, lst_txn_dob, od_avg_day_12mth, od_intvl_max, od_tran_cnt_1mth, rule_els, td_90d, bqs_90d
          |from ccard_bigtable11
          |where shouyu = 0 and max_due > 0 and max_due < 9
        """.stripMargin

    val group4Sql: String =
        """
          |select member_code1, od_paid_amt_incre_trend, od_trade_cnt_3mth, tran_paid_amt_incre_1mth, max_due, od_avg_day_12mth, od_intvl_max, od_paid_ratio_12mth, od_tran_cnt_1mth, od_paid_amt_prt_12mth, tran_paid_amt_prt_12mth
          |from ccard_bigtable11
          |where shouyu = 0 and max_due > 8
        """.stripMargin

    val shiDongPaidSql: String =
        """
          |select member_code, date_format(lst_upd_time, 'yyyy-MM-dd') lst_upd_time, sum(tran_amt_paid) tran_amt_paid, cash_amt
          |from whmd_hisdue1_11
          |where tran_amt_paid > 0 and lst_upd_time < current_date and payment_dte < current_date
          |group by member_code, lst_upd_time, cash_amt
          |order by member_code, lst_upd_time desc
        """.stripMargin

    val shiDongLoanSql: String =
        """
          |select member_code, date_format(posting_dte, 'yyyy-MM-dd') posting_dte, sum(tran_amt) tran_amt, cash_amt
          |from  whmd_hisdue1_11
          |where posting_dte < current_date
          |group by member_code, posting_dte, cash_amt
          |order by member_code, posting_dte
        """.stripMargin

    val odIntvlMaxSql: String =
        """
          |select member_code, collect_list(date_format(payment_dte,'yyyy-MM-dd'))
          |from (select * from whmd_hisdue1_11 where due > 0 order by member_code,payment_dte asc)
          |group by member_code
        """.stripMargin

    def checklistTmpSql(result: String): String = {
        "(select a.pay_user_id,a.update_time,a.create_time,a.tdscore,b.rule_id,b.rule_name,b.rule_package_name,case when rule_id in ('65273','65272','65269','65268','65265','65264','65263','65262','65261','65260','65259','65258') then substr(remark,4,instr(remark,',')-4 ) when rule_id in ( '65252','65251','65245','65244','65213','65212','65206','65205') then substr(remark,6,instr(remark,'#')-6) else to_char(b.score) end as score_new from frms_portal_credit.rams_cdt_checklist  a inner join frms_portal_credit.rams_cdt_checklist_risks b on a.id=b.checklist_id where a.pay_user_id in (" + result + ") and trunc(a.create_time)>trunc(sysdate)-545) t"
    }
}
